#!/usr/bin/env python
# -*- coding: utf-8 -*-

''' Autor: Neuza Figueira '''

import sqlite3
import csv
from operator import itemgetter

''' Class to access data from database '''
class Statistics:
    def __init__(self):
        self.theList = []
                
        connection = sqlite3.connect("Database.db")

        cursor = connection.cursor()

        cursor.execute('''select * from Database''')

        for line in cursor.fetchall():
            
            self.theList.append([line[0],
                                 line[1].encode('utf-8').replace('♦', '"'),
                                 line[2].encode('utf-8').replace('♦', '"'),
                                 line[3].encode('utf-8').replace('♦', '"'),
                                 line[4].encode('utf-8').replace('♦', '"'),
                                 line[5].encode('utf-8').replace('♦', '"'),
                                 line[6].encode('utf-8').replace('♦', '"'),
                                 line[7].encode('utf-8').replace('♦', '"'),
                                 line[8].encode('utf-8').replace('♦', '"')])            
            pass

	self.degree = set()
	self.categorie = set()
        self.year = set()
        self.establishment = set()
        
        for line in self.theList:
	    self.degree.add(line[2])
	    self.categorie.add(line[4])
            self.year.add(line[6])
            self.establishment.add(line[8])
            
            pass

	self.degree = list(self.degree)
	self.categorie = list(self.categorie)
        self.year = list(self.year)
        self.establishment = list(self.establishment)       

	self.degree.sort()
	self.categorie.sort()
        self.year.sort()
        self.establishment.sort()
                
        connection.close()
        pass
    
    
    # number of establishments per year
    def countStatistic1(self, year, establishment):
        total = 0
        for line in self.theList:
            if (year == line[6] and establishment == line[8]):
                total = total + 1
                pass
            pass
        return total
	print total
    
   
    
    # number of teachers divided per professional categories per establisment and per year
    def countStatistic2(self, year, establishment, categorie):
        total = 0
        for line in self.theList:
            if (categorie == line[4] and year == line[6] and establishment == line[8]):
                total = total + 1
                pass
            pass        
        return total

    

    # number of teachers divided per type of degree per establishment, per professional category and per year
    def countStatistic3(self, year, establishment, degree, categorie):
        total = 0
        for line in self.theList:
            if (degree == line[2] and categorie == line[4] and year == line[6] and establishment == line[8]):
                total = total + 1
                pass
            pass        
        return total

    # creates the csv file for number of establishments per year statistic
    def createCSVStatistic1(self, establishment):
        theFile = open('CSVStatistic1.csv', 'wb')
        spamWriter = csv.writer(theFile, delimiter=',')
        spamWriter.writerow(["Ano", "Estabelecimento", "Número de professores"])
        for year in self.year:
            data1 = self.countStatistic1(year, establishment)
            spamWriter.writerow([year, establishment, data1])      
            pass
        theFile.close()
        pass


# creates the csv file for number of teachers divided per professional categories per establisment and per year
    def creatCSVStatistic2(self, establishment, categorie ):
        theFile = open('CSVStatistic2.csv', 'wb')
        spamWriter = csv.writer(theFile, delimiter=',')
        spamWriter.writerow(["Ano", "Estabelecimento", "Categoria", "Número de professores"])
        for year in self.year:
            data2 = self.countStatistic2(year, establishment, categorie)
            spamWriter.writerow([year, establishment, categorie, data2])
            pass
        theFile.close()
        pass


    # creates the csv file for number of teachers divided per type of degree per establishment, per professional category and per year
    def creatCSVStatistic3(self, establishment, degree, categorie):
        theFile = open('CSVStatistic3.csv', 'wb')
        spamWriter = csv.writer(theFile, delimiter = ',')
        spamWriter.writerow(["Ano", "Estabelecimento", "Categoria", "Último Grau", "Número de Professores"])
        for year in self.year:
            data3 = self.countStatistic3(year, establishment, degree, categorie)
            spamWriter.writerow([year, establishment, categorie, degree, data3])
            pass
        theFile.close()
        pass
  

    # creates the csv file for number of establishments per year statistic
    def HTMLStatistic1(self):
        theFile = file("IndexStatistic.html", "w")
        theFile.write("<html> <body>")
        theFile.write('<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> </head> <body>')

        theFile.write('<h3>Ano - Nome do Estabelecimento - Nome do Professor</h3>')
        listTemp = sorted(self.theList, key=itemgetter(8,6,1))
        
        for line in listTemp:
            theFile.write('<li>' + line[6] + ' - ' + line[8] + ' - ' + line[1])
            pass
        
        theFile.write("</html> </body>") 
        theFile.close()
        pass
    
    # creates the csv file for number of teachers divided per professional categories per establisment and per year
    def HTMLStatistic2(self):
        theFile = file("IndexStatistic.html", "w")
        theFile.write("<html> <body>")
        theFile.write('<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> </head> <body>')
        theFile.write('<h3>Ano - Nome do Estabelecimento - Categoria - Nome do Professor</h3>')
        listTemp = sorted(self.theList, key=itemgetter(4, 8, 6, 1))
        
        for line in listTemp:
            theFile.write('<li>'+ line[6] + ' - ' + line[8] + ' - ' + line[4] + ' - ' + line[1])
            pass
        
        theFile.write("</html> </body>") 
        theFile.close()
        pass

    # creates the csv file for number of teachers divided per type of degree per establishment, per professional category and per year
    def HTMLStatistic3(self):
        theFile = file("IndexStatistic.html", "w")
        theFile.write("<html> <body>")
        theFile.write('<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> </head> <body>')
        listTemp = sorted(self.theList, key=itemgetter(2, 8, 6, 1))
        theFile.write('<h3>Ano - Nome do Estabelecimento - Categoria - Último Grau - Nome do Professor</h3>')
        for line in listTemp:
            theFile.write('<li>'+ line[6] + ' - ' + line[8] + ' - ' + line[4] + ' - ' + line[2] + ' - ' + line[1])
            pass
        
        theFile.write("</html> </body>") 
        theFile.close()
        pass


